﻿#region << 版 本 注 释 >>
/*----------------------------------------------------------------
 * 版权所有 (c) 2021  NJRN 保留所有权利。
 * CLR版本：4.0.30319.42000
 * 机器名称：杜学礼
 * 公司名称：
 * 命名空间：BusinessRule.Warning
 * 唯一标识：1d559b82-e492-4bc7-a616-0d2de5b778b7
 * 文件名：U8Data
 * 当前用户域：杜学礼
 * 
 * 创建者：DELL
 * 电子邮箱：（你的电子邮箱地址）
 * 创建时间：2021/12/7 9:14:04
 * 版本：V1.0.0
 * 描述：
 *
 * ----------------------------------------------------------------
 * 修改人：
 * 时间：
 * 修改说明：
 *
 * 版本：V1.0.1
 *----------------------------------------------------------------*/
#endregion << 版 本 注 释 >>

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Threading.Tasks;
using BusinessRepository;
using learun.util;

namespace BusinessRule.Warning
{
    /// <summary>
    /// U8Data 的摘要说明
    /// </summary>
    public class U8Data
    {
        public static async Task<DataTable> GetZCKP(string zcbm)
        {
            string sql = @"select gddm as assetcode,gdmc as assetname,(case when left(lbdm,2)='20' then '2' else '1' end) as assetclass,
(case lbdm when '1001' then '65' when '1002' then '66' when '1003' then '67' when '1004' then '67' when '1005' then '67' when '1006' then '67' when '1007' then '67' when '1008' then '67'
 when '200106' then '74' when '200101' then '74' when '200102' then '74' when '200103' then '74' when '200104' then '74' when '200105' then '74'  when '200107' then '74' when '200108' then '74'
  when '200201' then '73' when '200202' then '73' when '200203' then '73' when '200204' then '73' when '200205' then '73' when '200206' then '73' when '200601' then '69'
  when '200602' then '69' when '3001' then '70' when '3002' then '70' when '5001' then '71' when '5002' then '71' when '5003' then '71' else '65' end) as assettype,
  (case lbdm when '200101' then '292' when '200102' then '293' when '200103' then '294' when '200104' then '295' when '200105' then '296' when '200106' then '297'
  when '200107' then '298' when '200201' then '299' when '200202' then '300' when '200206' then '324'
  when '200203' then '325' when '200204' then '326' when '200205' then '327' when '200501' then '317' when '200502' then '329' when '2004' then '330'
  when '200503' then '332' when '200601' then '333' when '200602' then '334' else '0' end) as resourcetype,isnull(a.ZLWZ,0) as assetsite,convert(varchar(20),a.JZMJ) as  tradearea,convert(varchar(20),a.JZMJ-isnull(e.yy,0)) as kymj,
  (case when left(lbdm,2)='20' then '1' else '2' end)  as tradeunit,left(gddm, 15) as orgid,BMMC,b.orgname,b.org,GDDW 
from vw_fa_kp a left join Org b on left(gddm,15)=b.org 
left join
(select c.assetcode,sum(tradearea) as yy from Assets c left join Contract d on c.projcode=d.projcode where contractenddate>GETDATE() group by c.assetcode) e on a.GDDM=e.assetcode where gddm='" + zcbm+"'";
            return await new RepositoryFactory().BaseRepository().FindTable(sql,null);
        }

        public static async Task<(DataTable,int)> GetListZCKP(string org, string zcbm, string zcmc, string lbdm, int current, int size)
        {
           
            string sql = @"select gddm as assetcode,gdmc as assetname,(case when left(lbdm,2)='20' then '2' else '1' end) as assetclass,
(case lbdm when '1001' then '65' when '1002' then '66' when '1003' then '67' when '1004' then '67' when '1005' then '67' when '1006' then '67' when '1007' then '67' when '1008' then '67'
 when '200106' then '74' when '200101' then '74' when '200102' then '74' when '200103' then '74' when '200104' then '74' when '200105' then '74'  when '200107' then '74' when '200108' then '74'
  when '200201' then '73' when '200202' then '73' when '200203' then '73' when '200204' then '73' when '200205' then '73' when '200206' then '73' when '200601' then '69'
  when '200602' then '69' when '3001' then '70' when '3002' then '70' when '5001' then '71' when '5002' then '71' when '5003' then '71' else '65' end) as assettype,
  (case lbdm when '200101' then '292' when '200102' then '293' when '200103' then '294' when '200104' then '295' when '200105' then '296' when '200106' then '297'
  when '200107' then '298' when '200201' then '299' when '200202' then '300' when '200206' then '324'
  when '200203' then '325' when '200204' then '326' when '200205' then '327' when '200501' then '317' when '200502' then '329' when '2004' then '330'
  when '200503' then '332' when '200601' then '333' when '200602' then '334' else '0' end) as resourcetype,isnull(a.ZLWZ,0) as assetsite,convert(varchar(20),a.JZMJ) as  tradearea,convert(varchar(20),a.JZMJ-isnull(e.yy,0)) as kymj,
  (case when left(lbdm,2)='20' then '1' else '2' end)  as tradeunit,left(gddm, 15) as orgid,BMMC,b.orgName,b.org,GDDW 
from vw_fa_kp a left join Org b on left(gddm,15)=b.org 
left join
(select c.assetcode,sum(tradearea) as yy from Assets c left join Contract d on c.projcode=d.projcode where contractenddate>GETDATE() group by c.assetcode) e on a.GDDM=e.assetcode
 where 1=1 ";
            if (ConfigHelper.GetConfig().areacode == "096")
            {
                sql += " and lbdm in ('10','50','1001','5001','5002','5003') ";
            }
            if (ConfigHelper.GetConfig().areacode == "096")
            {
                if (org != "")
                {
                    sql += " and gsdm like '" + org + "%' ";
                }
            }
            else
            {
                if (org != "")
                {
                    sql += " and gsdm in (select (case when ISNULL(oldorg,'')='' then org else oldorg end) from Org where org like '" + org + "%') ";
                }
            }
            if (zcbm != "")
            {
                sql += " and gddm like '%" + zcbm + "%'";
            }
            if (zcmc != "")
            {
                sql += " and gdmc like '%" + zcmc + "%'";
            }
            if (lbdm != "")
            {
                sql += " and lbdm like '%" + lbdm + "%'";
            }
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null, "assetcode", false,size,current);
        }

        public static async Task<DataTable> GetLBDM()
        {
            string sql = "select distinct lbdm,lbmc from FA_Gdlb ";
            if (ConfigHelper.GetConfig().areacode == "096")
            {
                sql += " where lbdm in ('10','50','1001','5001','5002','5003')";
            }
            return await new RepositoryFactory().Repository().FindTable(sql, null);
        }

        public static async Task<DataTable> GetSYZK()
        {
            string sql = "SELECT distinct zkdm,zkmc FROM VW_FA_Syzk";
            return await new RepositoryFactory().BaseRepository().FindTable(sql, null);
        }

        public static async Task<DataTable> GetKmyeb(string orgcode, string kjqj)
        {
            string sql = @"select a.kjnd,a.kjqj,a.kmdm,b.kmmc,yeblx,a.gsdm,c.hsdwmc as gsmc,a.zth,c.ztmc,sum(jfje) as jfje,sum(dfje) as dfje from gl_view_yeb a left join GL_KMXX b 
on a.kjnd=b.kjnd and a.gsdm=b.gsdm and a.zth=b.ZTH and a.kmdm=b.kmdm
                          left join GL_Ztcs c on a.gsdm = c.hsdwdm and a.zth = c.ztbh and a.kjnd = c.kjnd  where a.kjqj='" + kjqj+ "' and a.gsdm='" + orgcode + "'  group by a.kjnd,a.kjqj,a.kmdm,b.kmmc,yeblx,a.gsdm,c.hsdwmc,a.zth,c.ztmc";
           
            return await new RepositoryFactory().Repository().FindTable(sql,null);
        }

        public static async Task<DataTable> getZTDW(string kjnd)
        {
            string sql = "select hsdwdm,hsdwmc from GL_Ztcs where kjnd='"+kjnd+ "' and ztbh<>'99999999999999999999' and hsdwdm like '320571050%'";
            return await new RepositoryFactory().Repository().FindTable(sql,null);
        }

       
    }
}
